USE [HQPad]
GO
/****** Object:  StoredProcedure [dbo].[hqpad_character_allowed_skills]    Script Date: 07/19/2013 11:29:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Michael Harper-Jones
-- Create date: 15 June 2012
-- Description:	Extract the list of allowed skills for a character.
--              Returns skill table records.
-- =============================================
ALTER PROCEDURE [dbo].[hqpad_character_allowed_skills]
	-- Add the parameters for the stored procedure here
	@rulesId int, -- the rule set id.
	@skills varchar(max), -- comma list of skill slugs (those already bought by character).
	@ranks varchar(max), -- comma list of skill ranks (in same order as @skills).
	@guildLevel int, -- the character guild rank.
	@primaryGuild varchar(250), -- the character primary guild slug (or null).
	@secondaryGuild varchar(250), -- the character secondary guild slug (or null).
	@tertiaryGuild varchar(250), -- the character tertiary guild slug (or null).
	@characterRank int, -- the character rank.
	@pointsTableId int, -- the character points table id.  skill not available if no cost record for table.
	@showAllSkills bit
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @skillSet Table
	(
		SkillID int,
		Allowed bit
	)
	
	-- convert skills list to table
	declare @skillTable Table
	(
		SkillID int,
		Ranks int
	)
	
	-- populate skills table
	insert into @skillTable
	select SkillID, CONVERT(int, y.Item)
	from Skill sk
	inner join udf_ItemParse(@skills, ',') x on x.Item = sk.Slug
	inner join udf_ItemParse(@ranks, ',') y on y.Pos = x.Pos
	where sk.RuleSetID = @rulesId
	
	--select * from @skillTable

    -- Insert statements for procedure here
    insert into @skillSet (SkillID, Allowed)
	SELECT sk.SkillID, 1 
	from Skill sk
	inner join SkillCost sc on sc.SkillID = sk.SkillID and sc.PointsTableID = @pointsTableId
	--inner join SkillRequirement sr on sr.ParentSkillID = sk.SkillID
	where 
	-- not deleted skills.
	sk.Deleted = 0
	-- only in rule set.
	and sk.RuleSetID = @rulesId
	-- any basic skill.
	and (sk.IsBasic = 1)
	or
	-- any skill without any requirements.
	( (select COUNT(*) from SkillRequirement sr where sr.ParentSkillID = sk.SkillID) = 0)
	or
	(
		-- any guild (and guild level) requirements met.
		(
			-- no guild level requirements.
			(select count(*) from SkillRequirement sr 
			where sr.ParentSkillID = sk.SkillID
			and sr.GuildID is not null) = 0
		or
			-- any guild level requirement met.
			(select count(*) from SkillRequirement sr 
			where sr.ParentSkillID = sk.SkillID
			and sr.GuildID is not null
			and sr.GuildID in (
				select GuildID 
				from Guild g 
				where g.RuleSetID = @rulesId 
				and (g.Slug = @primaryGuild or g.Slug = @secondaryGuild or g.Slug = @tertiaryGuild)
			)) > 0
		)
	
		-- all 'and' non-guild requirements met.
		and (
			-- all 'and' requirements met (count those that meet and compare to total).
			(select COUNT(*) 
			from SkillRequirement sr
			left join @skillTable st on st.SkillID = sr.RequiredSkillID
			where sr.ParentSkillID = sk.SkillID
			and sr.GuildID is null
			and sr.Combination = 'AND'
			and (
				(sr.MinRank is not null and sr.MinRank <= @characterRank) -- min rank passed.
				or
				(sr.SkillRequirementID is not null and st.SkillID is not null and coalesce(sr.QuantityRequired,1) <= st.Ranks) -- required skill present
			))
			=
			(select COUNT(*) 
			from SkillRequirement sr
			where sr.ParentSkillID = sk.SkillID
			and sr.GuildID is null
			and sr.Combination = 'AND')
		)
		
		-- any 'or' non-guild requirement met.
		and (
			-- no 'or' requirements.
			(select COUNT(*) 
			from SkillRequirement sr
			where sr.ParentSkillID = sk.SkillID
			and sr.GuildID is null
			and sr.Combination = 'OR') = 0
			
			or
			
			-- any 'or' requirement.
			(select COUNT(*) 
			from SkillRequirement sr
			left join @skillTable st on st.SkillID = sr.ParentSkillID
			where sr.ParentSkillID = sk.SkillID
			and sr.GuildID is null
			and sr.Combination = 'OR'
			and (
				(sr.MinRank is not null and sr.MinRank <= @characterRank) -- min rank passed.
				or
				(sr.SkillRequirementID is not null and st.SkillID is not null and sr.QuantityRequired <= st.Ranks) -- required skill present
			)) > 0
		)
		
	)
	
	if (@showAllSkills = 1)
	begin
		-- Add in all guild skills that are not already in the list whether allowed or not.
		insert into @skillSet (SkillID, Allowed)
		select distinct sr.ParentSkillID, 0 
		from SkillRequirement sr 
		where sr.GuildID is not null
		and (sr.GuildID in (
			select GuildID 
			from Guild g 
			where g.RuleSetID = @rulesId 
			and (g.Slug = @primaryGuild))
			
			or
			
			(sr.GuildID in (
				select GuildID 
				from Guild g 
				where g.RuleSetID = @rulesId 
				and (g.Slug = @primaryGuild or g.Slug = @secondaryGuild or g.Slug = @tertiaryGuild))
			and sr.PrimaryGuildOnly = 0
			)
		)
		and sr.GuildLevel <= @guildLevel
		and sr.ParentSkillID not in (select SkillID from @skillSet)
		-- exclude any failed min rank requirements
		and not exists (
			select * from SkillRequirement sr2
			where sr2.ParentSkillID = sr.ParentSkillID
			and sr2.MinRank is not null
			and sr2.MinRank > @characterRank)
			
			
		-- add in skills that have no guild requirement or min rank requirements
		-- and are not already in the list.
		insert into @skillSet (SkillID, Allowed)
		select sk.SkillID, 0
		from Skill sk
		where 
		-- not deleted skills.
		sk.Deleted = 0
		-- only in rule set.
		and sk.RuleSetID = @rulesId
		and sk.SkillID not in (select SkillID from @skillSet)
		and not exists (
			select * from SkillRequirement sr
			where sr.ParentSkillID = sk.SkillID
			and sr.GuildID is not null
		)
		-- exclude any failed min rank requirements
		and not exists (
			select * from SkillRequirement sr2
			where sr2.ParentSkillID = sk.SkillID
			and sr2.MinRank is not null
			and sr2.MinRank > @characterRank)
		and sk.TypeOfSkill != 'Psi'
	end
	
	SELECT sk.*, ss.Allowed 
	from Skill sk
	inner join @skillSet ss on ss.SkillID = sk.SkillID
	
END
